package edu.unc.comp410.colemangeer.data;

import static java.lang.System.err;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import edu.unc.comp410.colemangeer.model.*;
import edu.unc.comp410.sharedcode.SQLutilities;
import edu.unc.comp410.sharedcode.ValidateUtilities;

public class ProductGateway implements IProductGateway{
	
		 private class SimpleProductCache implements IProductCache{
			 
			private Map<Integer,Product> products =
				new HashMap<Integer,Product>();
			
			public Product getProductById(Integer id) {
				System.err.println("getProductById() called");
				return products.get(id);
			}

			public void addProduct(Product p){
				products.put(p.getId(), p);
			}
			private void addProducts(Set<Product> products){
				for (Product p: products){
					addProduct(p);
				}
			}
			private void clear(){
				products.clear();
			}

			public void removeProductById(Integer id){
				products.remove(id);
			}
			
			/* (non-Javadoc)
			 * @see edu.unc.comp410.colemangeer.data.IProductCache#hasProduct(java.lang.Integer)
			 */
			public boolean hasProduct(Integer id){
				return products.containsKey(id);
			}
		}
	
		// Data members.
		private Connection conn;
		private Statement stmt;
		private String DB = "customers1"; 
		private String MACH = "classroom";
		private static Logger log = Logger.getLogger( ProductGateway.class );
		private static Integer nextUnusedId = 1000;
		private IProductCache cache;
		
		/** 
		 * Default constructor
		 */		
		public ProductGateway( String db, String mach){
			ValidateUtilities.checkForContent( db );
			ValidateUtilities.checkForContent( mach );
			DB = db;
			MACH = mach;
			conn = SQLutilities.getConnection( db, mach );
			stmt = SQLutilities.getStatement( conn );
			cache = new SimpleProductCache(); // does JMock need this to be null?
		}

		// this is for use in testing
		protected void setCache(IProductCache cache){
			this.cache = cache;
		}
		
		/**
		 * @requires theRS.next() has been executed and there is another
		 *    row to process
		 * @param rs
		 * @return
		 */
		private Product getProductFromDatabase( ResultSet rs ) {
			Product result = null;
			try {				
				Integer ProductId = rs.getInt( "ProductNum" );
				String Name = rs.getString( "Name" ); //TODO: this has to match wherever the resultSet gets made
				Double price = rs.getDouble( "Price" );
		
				List<ProductCategory> categoryList = new ArrayList<ProductCategory>();
						
				String[] categoryStrings;
				String catString = rs.getString("Category");
				
				if (catString != null){
					
				categoryStrings = catString.split(",");

				ProductCategory cat;
				for (String s: categoryStrings){
					log.info("Category is " + s);
					cat = ProductCategory.getCategoryByName(s.trim());
					if (cat!=null)
						categoryList.add(cat);
				}
				}
				result = new Product( ProductId, Name, price, categoryList );
				
			} catch( SQLException e ) {
				System.err.println( e );
				e.printStackTrace();
			} 
			return result;
		}
		
		private Integer getResultCount( ResultSet rs ) {
			Integer result = null;
			try {				
				result = rs.getInt( "Count" ); //TODO: make this work by looking up what "select count(*)" has as a field title
				
			} catch( SQLException e ) {
				System.err.println( e );
				e.printStackTrace();
			} 
			return result;
		}
	
		/**
		 * Search the Customer table of the DB for the single entry
		 *    with CustomerID = searchID.
		 * @param searchID = CustomerID to search DB for.
		 * @return the single Customer entry with CustomerID = searchID,
		 *    null if no match
		 */
		public Product getByID( Integer searchID ) {		
			Product result = null;
			
			//try cache first

			Product cacheResult = null;
			try {
				cacheResult = cache.getProductById(searchID);
			} catch (NullPointerException e1) {
			}
			
			if (cacheResult != null){	
				return cacheResult;
			}				

			
			// Search DB.
			final String query = "SELECT ProductNum, Name, Price, Category "
				+ "FROM Product WHERE ProductNum = " + searchID;
			log.debug( "ProductGateway: findById: query = " + query );
			try {
				ResultSet rs = stmt.executeQuery( query );
				if( rs.next() ) result = getProductFromDatabase( rs );
			} catch( SQLException e ) {
				err.println( e );
				e.printStackTrace();
			}
			catch (IllegalArgumentException e){
				log.error("Illegal argument: can not make Product object");
			}
			
			//cache result for future use
			try {
				cache.addProduct(result);
			} catch (NullPointerException e) {
			}
			return result;
		}
		
		public Set<Product> getAllProducts(){
			
			Product product = null;
			Set<Product> resultSet = new HashSet<Product>();
			
			// Search DB.
			final String query = "SELECT ProductNum, Name, Price, Category "
				+ "FROM Product;";
			log.debug( "ProductGateway: getAllProducts: query = " + query );
			
			try {
				ResultSet rs = stmt.executeQuery( query );
				while( rs.next()) {
					try {
						product = getProductFromDatabase( rs );
					} catch (IllegalArgumentException e) {
						log.error("Entry in Product data from db has illegal argument: no Product can be made from it");
						continue;
					}
					resultSet.add(product);
				}
			} catch( SQLException e ) {
				err.println( e );
				e.printStackTrace();
			}
			
			
			return resultSet;
			
		}
		
		public Integer getTableSize() {
			Integer result=null;
			final String query = "SELECT count(*) as Count "
				+ "FROM Product";
			log.debug( "ProductGateway: getTableSize: query = " + query );
			try {
				ResultSet rs = stmt.executeQuery( query );
				if( rs.next() ) result = getResultCount( rs );
			} catch( SQLException e ) {
				err.println( e );
				e.printStackTrace();
			}
			return result;
		}
		
		/**
		 * Search the Customer table of the DB for all entries
		 * that contain nameSubstring as a substring in the Name field.
		 * @param nameSubstring
		 * @return all Customer entries that contain nameSubstring
		 *     as a substring in the Name field.
		 */
		public Set<Product> getByName( String nameSubstring ) { 
			Set<Product> result = new HashSet<Product>();
			//TODO: fix this query to match the DB once I can connect
			String query = "SELECT ProductNum, Name, Price, Category  "
				+ "FROM Product WHERE Name LIKE '%"+ nameSubstring + "%'";
			log.debug( query );
			try {
				ResultSet rs = stmt.executeQuery( query );
				while( rs.next() ) {
					Product c = getProductFromDatabase( rs );
					log.debug( "ProductGateway.getByName: c = " + c );
					result.add( c );
				}
			} catch (SQLException e) {
				err.println( e );
				e.printStackTrace();
			}
			return result;
		}
		
		
		public Integer getNextUnusedId() {
			return nextUnusedId++;
		}
		
		//	public String getDB() { return DB; }
		//	public String getMACH() { return MACH; }
		//	
		//	/**
		//	 * We were successful in obtaining both a DB
		//	 * connection and a DB statement?
		//	 */
		public boolean successfulDBconnection() {
			return( null != conn && null != stmt );
		}
		
		/**
		 * 
		 */
		@Override public String toString() {
			String result = "ProductGateway: db = " + DB
				+ " machine = " + MACH;
			return result;
		}
			
		/**
		 * Too dangerous to have a clear that really
		 * does wipe out the DB table.
		 */
		public void clear() {
		}	
		
		/**
		 * Delete one entry
		 * @param id
		 * @return number of rows delete (should always be 0 or 1)
		 */
		public int deleteById( int id ) {
			final String update = "DELETE FROM Product WHERE Id = "
				+ id;
			log.debug( update );
			int result = 0;
			try {
				result = stmt.executeUpdate( update );
			} catch (SQLException e) {
				err.println( e );
				e.printStackTrace();
			}
			try {
				cache.removeProductById(id);
			} catch (NullPointerException e) {
			}
			return result;
		}
	
		public int size() {
			int result = 0;
			//TODO: fix this query to match the DB once I can connect
			final String query = "SELECT COUNT(*) AS Size FROM Product";
			log.debug( "size: query = " + query );
			try {
				ResultSet rs = stmt.executeQuery( query );
				if( rs.next() ) {
					result = rs.getInt( "Size" );
				}
			} catch (SQLException e) {
				err.println( e );
				e.printStackTrace();
			}
			return result;
		}
}
